--- layout: post title: 'Mortgage Refinance Calculator Part 2: Visuals' author: Coy McNew date: '2021-06-09 9:00:00' --- Mortgage Refinance Calculator Part 2: Visuals

Objective

As mortgage interest rates continued to decline through early 2021, I decided to see how much we could save by refinancing. Since I knew I would be shopping around from different sources who offer different rates, closing costs, points, etc. I thought this would be a good time to crank out another Shiny tool. The objectives of this tool were as follows:

  1. Calculate costs of refinanced mortgage as compared to current mortgage for any user-supplied values.
  2. Display simple summaries of the above information in an interactive, investigable format.
  3. Convince my wife that it was time to refinance our home.

In this post I’m going to cover the visuals I built to display the results of the calculations from Part 1. In the next post, I’ll summarize the Shiny app layout.

Overall Running Total

First, I wanted to generate an overall time series plot throughout the life of each loan so I can make direct comparisons between my current mortgage and potential refinanced mortgage. I basically wanted this figure to tell the whole story at a glance so it was important that I keep it as simple and readable as possible while also providing an opportunity to investigate the data in detail, so it has to be interactive. Without a doubt, plotly is the way to go here. It makes clean, intuitive, and interactive graphics which work great in Shiny apps.

I’ll start by taking a look at the original mortgage. I’m interested in comparing three key values between the original and refinanced mortgage: 1) principal paid, 2) interest paid, and 3) total paid. I’m plotting each of the three as it’s own trace so I can manually define the name, color, line type etc. If I were adding a bunch of lines here I’d want to handle this in a more vectorized way. I’m using origDF and refiDF which I generated in the previous post.

library(tidyverse)
library(plotly)
library(ggsci)

#generate some plotting colors
cols <- pal_jco()(5)

#overall plot for original mortgage
p <- plot_ly() %>%
  add_trace(data = origDF,
            x = ~date, y = ~ending_balance,
            name = "Balance",
            line = list(color = cols[1]),
            type = 'scatter', mode = 'lines'
  ) %>%
  add_trace(data = origDF,
            x = ~date, y = ~principal_paid,
            name = "Principal Paid",
            line = list(color = cols[2]),
            type = 'scatter', mode = 'lines'
  ) %>%
  add_trace(data = origDF,
            x = ~date, y = ~interest_paid,
            name = "Interest Paid",
            line = list(color = cols[3]),
            type = 'scatter', mode = 'lines'
  ) %>%
  layout(
    showlegend = FALSE,
    xaxis = list(title = "Date"), yaxis = list(title = "Running Total ($)")
  ) 
p

Ok, that looks pretty good. You’ll notice I loaded some colors for plotting from ggsci::pal_jco(). I really like the color palettes from ggsci. They tend to be clean and understated. Now I’ll add the same values from the refinanced mortgage as dotted lines.

p2 <- p %>%
  add_trace(data = refiDF,
            x = ~date, y = ~ending_balance,
            name = "Balance",
            line = list(color = cols[1], dash = "dot"),
            type = 'scatter', mode = 'lines'
  ) %>%
  add_trace(data = refiDF,
            x = ~date, y = ~principal_paid,
            name = "Principal Paid", 
            line = list(color = cols[2], dash = "dot"),
            type = 'scatter', mode = 'lines'
  ) %>%
  add_trace(data = refiDF,
            x = ~date, y = ~interest_paid,
            name = "Interest Paid", 
            line = list(color = cols[3], dash = "dot"),
            type = 'scatter', mode = 'lines'
  )
p2

Now I can directly compare any of the three measures between original and refinanced mortgage, zoom in, and tinker to my heart’s content. Now I’ll add a little polish with a unified hovermode in the x direction. This will show all values of each line for the x-position where the cursor hovers. It makes comparing corresponding y-values crystal clear and I think it looks really cool. I’m also going to add an annotation explaining the difference between solid and dotted lines. By referencing the paper rather than x,y for the annotation, it will always be visible no matter where I zoom and/or pan.

p2 %>%
  layout(hovermode = "x unified") %>%
  add_annotations(
    x= 0.5, y= 1,
        xref = "paper", yref = "paper",
        text = "Solid lines = original mortgage, dotted lines = refinanced",
        showarrow = F
  )

Ok, that has everything I need to compare the running totals of the two mortgages side-by-side. Now I’ll move on to the visuals needed for comparing monthly payments.

Monthly Payment

Ok so that visual does a great job showing us how the two stack up throughout the life of the loan, but what if I want to see how things differ from a monthly payment standpoint? Sometimes the goal of a refinance is simply to save money every month, not necessarily throughout the life of the loan. Since the monthly payment is unchanged from month to month, I think the most effective way to communicate this is through a fill plot. By color filling how much of each payment goes towards interest vs how much goes towards principal, I can really drill down on month to month differences between the two options.

I’ll start with the original mortgage.

porig <- plot_ly() %>%
    add_trace(data = origDF,
              x = ~date, y = ~principal_payment,
              name = "Principal",
              fillcolor = cols[5],
              type = "scatter", mode = "none", stackgroup = "one"
    ) %>%
    add_trace(data = origDF,
              x = ~date, y = ~interest_payment,
              name = "Interest", 
              fillcolor = cols[4],
              type = "scatter", mode = "none", stackgroup = "one"
    )

porig

Ok, that’s having the effect I want. It does a good job showing the high percentage of the first few payments that go towards interest rather than paying down principal. I’m going to add x unified hover, just like above, so I can more clearly compare interest and principal for a given month. I’m also going to add an annotation to make it clear this figure is for my original monthly payment, since my plan is to stack in on top of the refinanced version for aligned, vertical comparison.

porig <- porig %>%
  add_annotations(
    x= 0.5, y= 0.2,
    xref = "paper", yref = "paper",
    text = "Original Monthly Payment",
    showarrow = F,
    font = list(color = 'white', size = 14)
  ) %>%
  layout(hovermode = "x unified")
porig

Now I’ll just do the same thing for the refinanced monthly payment.

prefi <- plot_ly() %>%
    add_trace(data = refiDF,
              x = ~date, y = ~principal_payment,
              name = "Principal",
              fillcolor = cols[5],
              type = "scatter", mode = "none", stackgroup = "one"
    ) %>%
    add_trace(data = refiDF,
              x = ~date, y = ~interest_payment,
              name = "Interest", 
              fillcolor = cols[4],
              type = "scatter", mode = "none", stackgroup = "one"
    ) %>%
    add_annotations(
      x= 0.5, y= 0.2,
      xref = "paper", yref = "paper",
      text = "Refinanced Monthly Payment",
      showarrow = F,
      font = list(color = 'white', size = 14)
    ) %>%
    layout(hovermode = "x unified")
prefi

Now to cap this off I’m going to use plotly::subplot() to combine the two visuals. By using shareX = TRUE, I can force the x-axis to remain vertically aligned no matter how I zoom and/or pan on either of the plots. This is a really cool trick to boost the usefulness of the interactivity.

subplot(porig, prefi, shareX = TRUE, nrows = 2) %>%
      layout(
        xaxis = list(title = "Date"),
        yaxis = list(title = "Original Mortgage<br>Payment ($)"),
        yaxis2 = list(title = "Refinanced Mortgage<br>Payment ($)"),
        showlegend = FALSE
      )

Conclusion

Ok these two visuals look great and I think they tell a complete story. The first one lets me directly compare running totals throughout the life of the loan and the second lets me investigate how monthly payments compare between the two scenarios. It’s a lot of information to pack into two figures, but because they’re interactive the user can investigate away and extract the full story. In the next post I’m going to package all this up into a Shiny app.